Secrets of Excel and Access for Accounting! by Andrei Besedin

Author:Andrei Besedin [Andrei Besedin]
Language: eng
Format: epub

The formulas inserted into cells in the Financial Statements worksheets reports will return data summarized by criteria, that is, a summary of each level.

• Level 1, Category: Assets, Liabilities, Stockholder’s Equity and Income Statements.

• Level 2, Account Groups: Under the Assets section - Current Assets, Property, and Equipment (at Cost), Other Assets. Under the Liabilities and Stockholder’s Equity section — Current Liabilities, Long-Term Liabilities, Stockholder’s Equity.

• Level 3, Account Types: Under the Current Assets section (as an example) - Cash, Accounts Receivable, Inventories and Prepaid Expenses.

Balance Sheet Summary Levels

To define Names for the three column levels:

1. Select column A, and press <Ctrl+F3>.

2. In the Names in workbook box, type ‘Level1’, and then click OK.

3. Repeat the steps above and define the Names for column B (Level2) and column C (Level3).

Defining a Name for Column C, Level3

Notes List

This worksheet contains a list of Notes titles, details, and formulas.

The calculation of a Note number is automatically updated in worksheets containing reports in the Financial Statements.xls workbook.

Data Worksheet

There is one worksheet in this category, in which the trial balances figures are stored. Trial Balances Data

Trial Balances Data

The worksheet is structured as follows:

• Column A: Account Number

• Column B: Account Name

• Column C: Account Types, Level 3

• Column D: Account Groups, Level 2

• Column E: Category, Level 1

• Columns F and onwards: Monthly Trial Balance figures

To add a Validation list for selecting an Account Type to column C:

1. Select column C.

2. From the Data menu, select Validation, and then select the Settings tab.

3. From the Allow dropdown list, select List.

Showing the Data Validation Box

4. In the Source box, press <F3>, paste the Name Level3 and then click OK.

Showing the List of Data

TIP

To open the Validation dropdown list in a cell in column C, select the cell and press <Alt+Down Arrow>.

Creating a Custom Menu

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.